package com.campus.dao.impl;

import com.campus.dao.TeacherDao;
import com.campus.model.Student;
import com.campus.model.Teacher;
import com.campus.model.User;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

@Repository
@Slf4j
public class TeacherDaoImpl implements TeacherDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional
    public boolean addTeacher(Teacher teacher, User user) {
        String insertUserSql = "INSERT INTO t_sys_user (login_name, password, gender, birthday, phone, email, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        String insertRoleSql = "INSERT INTO t_sys_user_role (user_id, role_id) VALUES (?, ?)";
        String insertTeacherSql = "INSERT INTO t_manager_teacher (teacher_name, subject, profession, school, introduce, login_id) " +
                "VALUES (?, ?, ?, ?, ?, ?)";

        // 检查登录名是否已经存在
        if (isValidLoginName(user.getLoginName())) {
            log.error("登录名已经存在: {}", user.getLoginName());
            return false;
        }

        try {
            // 1. 插入到 t_manager_user 表
            jdbcTemplate.update(insertUserSql, user.getLoginName(), "123456", user.getGender(),
                    new java.sql.Date(user.getBirthday().getTime()), user.getPhone(), user.getEmail(), 0);//状态默认是0，未启用。

            // 2. 获取新插入的 LoginId
            Integer loginId = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Integer.class);

            if (loginId == null) {
                return false;
            }

            // 3. 插入到 t_sys_user_role 表，绑定角色
            jdbcTemplate.update(insertRoleSql, loginId, 2);  // 2 表示教师角色

            // 4. 插入到 t_manager_teacher 表
            teacher.setLoginId(loginId); // 设置 loginId
            int rowsAffected = jdbcTemplate.update(insertTeacherSql, teacher.getTeacherName(), teacher.getSubject(),
                    teacher.getProfession(), teacher.getSchool(), teacher.getIntroduce(), teacher.getLoginId());

            return rowsAffected > 0;

        } catch (Exception e) {
            // 如果发生异常，Spring 会自动回滚事务
            log.error("Error adding teacher: {}", e.getMessage());
            return false;
        }
    }

    @Override
    public Teacher getTeacherById(Integer id) {
        String sql = "SELECT * FROM t_manager_teacher WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            Teacher teacher = new Teacher();
            teacher.setId(rs.getInt("id"));
            teacher.setTeacherName(rs.getString("teacher_name"));
            teacher.setSubject(rs.getString("subject"));
            teacher.setProfession(rs.getString("profession"));
            teacher.setSchool(rs.getString("school"));
            teacher.setIntroduce(rs.getString("introduce"));
            teacher.setLoginId(rs.getInt("login_id"));
            return teacher;
        }, id);
    }

    @Override
    public List<Teacher> searchTeachers(String teacherName, String subject, String school) {
        StringBuilder sql = new StringBuilder("SELECT * FROM t_manager_teacher WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (teacherName != null && !teacherName.isEmpty()) {
            sql.append(" AND teacher_name LIKE ?");
            params.add("%" + teacherName + "%");
        }
        if (subject != null && !subject.isEmpty()) {
            sql.append(" AND subject = ?");
            params.add(subject);
        }
        if (school != null && !school.isEmpty()) {
            sql.append(" AND school = ?");
            params.add(school);
        }

        return jdbcTemplate.query(sql.toString(), (rs, rowNum) -> {
            Teacher teacher = new Teacher();
            teacher.setId(rs.getInt("id"));
            teacher.setTeacherName(rs.getString("teacher_name"));
            teacher.setSubject(rs.getString("subject"));
            teacher.setProfession(rs.getString("profession"));
            teacher.setSchool(rs.getString("school"));
            teacher.setIntroduce(rs.getString("introduce"));
            teacher.setLoginId(rs.getInt("login_id"));
            return teacher;
        }, params.toArray());
    }

    @Override
    public int updateTeacher(Integer id, Teacher teacher) {
        String updateTeacherSql = "UPDATE t_manager_teacher SET teacher_name = ?, subject = ?, profession = ?, school = ?, introduce = ? WHERE id = ?";

        return jdbcTemplate.update(updateTeacherSql, teacher.getTeacherName(), teacher.getSubject(),
                teacher.getProfession(), teacher.getSchool(), teacher.getIntroduce(), id);
    }

    @Override
    public int deleteTeacherById(Integer id) {
        // 1. 获取与教师关联的 login_id
        String getLoginIdSql = "SELECT login_id FROM t_manager_teacher WHERE id = ?";
        Integer loginId = jdbcTemplate.queryForObject(getLoginIdSql, Integer.class, id);

        if (loginId == null) {
            // 如果没有找到对应的教师，返回 0 表示未删除
            return 0;
        }

        // 2. 删除教师信息
        String deleteTeacherSql = "DELETE FROM t_manager_teacher WHERE id = ?";
        int teacherDeleted = jdbcTemplate.update(deleteTeacherSql, id);

        if (teacherDeleted > 0) {
            // 3. 删除 sys_user_role 表中与用户关联的角色信息
            String deleteUserRoleSql = "DELETE FROM t_sys_user_role WHERE user_id = ?";
            jdbcTemplate.update(deleteUserRoleSql, loginId);

            // 4. 删除与老师关联的用户信息
            String deleteUserSql = "DELETE FROM t_sys_user WHERE id = ?";
            jdbcTemplate.update(deleteUserSql, loginId);
        }

        return teacherDeleted; // 返回删除的教师信息行数
    }

    @Override
    public List<Teacher> findAllTeachers() {
        String sql = "SELECT * FROM t_manager_teacher";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Teacher.class));
    }

    // Check if login_name is duplicate
    private boolean isValidLoginName(String loginName) {
        String sql = "SELECT COUNT(*) FROM t_sys_user WHERE login_name = ?";
        try {
            Integer count = jdbcTemplate.queryForObject(sql, new Object[]{loginName}, Integer.class);
            return count > 0;  // If count > 0, login name is duplicated
        } catch (EmptyResultDataAccessException e) {
            // No result found, login name is valid (not duplicated)
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;  // If there's an error, consider it invalid
        }
    }

}
